package JDBC.练习_增删改查;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Scanner;

public class Test {
    //创建集合存放对象
    static ArrayList<Brand> brandList = new ArrayList<>();
    //创建集合存放id
    static ArrayList<Integer> idList = new ArrayList<>();
    static Scanner sc = new Scanner(System.in);
    //获取连接
    static Connection cnt;

    static {
        try {
            cnt = getConnection();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) throws Exception {
        addToList();
        while (true) {
            System.out.println("请输入你的选择：\n" +
                    "1：查询\n" +
                    "2：添加\n" +
                    "3：修改\n" +
                    "4：删除\n");
            String chooseStr = sc.nextLine();
            int choose = Integer.parseInt(chooseStr);
            if (choose == 1) {
                //查询
                select();
                break;
            } else if (choose == 2) {
                //添加
                insert();
                break;
            } else if (choose == 3) {
                //修改
                update();
                break;
            } else if (choose == 4) {
                //删除
                delete();
                break;
            } else {
                System.out.println("选项不存在！");
            }
        }
    }

    //获取连接
    private static Connection getConnection() throws Exception {
        String rootDir = System.getProperty("user.dir");
        Properties prop = new Properties();
        prop.load(new FileReader(rootDir + "\\mysqlData\\druidPropExercise.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(prop);
        Connection cnt = ds.getConnection();
        return cnt;
    }

    //删除
    private static void delete() throws SQLException {
        //删除语句（根据id）
        String delSQL = """
                delete
                from tb_brand
                where id = ?;
                """;
        PreparedStatement delPreSta = cnt.prepareStatement(delSQL);

        while (true) {
            System.out.print("输入需要删除的品牌id：");
            String idStr = sc.nextLine();
            int id = Integer.parseInt(idStr);
            if (!idList.contains(id)) {
                System.out.println("id不存在！");
                continue;
            }

            delPreSta.setInt(1, id);
            int delLine = delPreSta.executeUpdate();
            if (delLine > 0) {
                System.out.println("删除成功！");
                //addToList(cnt);
                select();
            } else {
                System.out.println("删除失败！");
            }
            break;
        }
    }

    //修改
    private static void update() throws SQLException {
        //修改语句（根据id）
        String updSQL = """
                update tb_brand
                set brand_name  =?,
                    company_name=?,
                    ordered=?,
                    description=?,
                    status=?
                where id = ?;
                """;
        PreparedStatement updPreSta = cnt.prepareStatement(updSQL);
        while (true) {
            System.out.print("输入需要修改的品牌id：");
            String idStr = sc.nextLine();
            int id = Integer.parseInt(idStr);
            if (!idList.contains(id)) {
                System.out.println("id不存在！");
                continue;
            }

            System.out.print("输入修改后的的品牌名称：");
            String newGrandName = sc.nextLine();
            System.out.print("输入修改后的的企业名称：");
            String newCompanyName = sc.nextLine();
            System.out.print("输入修改后的的排序字段：");
            String newOrderedStr = sc.nextLine();
            int newOrdered = Integer.parseInt(newOrderedStr);
            System.out.print("输入修改后的的品牌描述：");
            String newDescription = sc.nextLine();
            System.out.print("输入修改后的的状态：");
            String newStatusStr = sc.nextLine();
            int newStatus = Integer.parseInt(newStatusStr);

            updPreSta.setString(1, newGrandName);
            updPreSta.setString(2, newCompanyName);
            updPreSta.setInt(3, newOrdered);
            updPreSta.setString(4, newDescription);
            updPreSta.setInt(5, newStatus);
            updPreSta.setInt(6, id);

            int updLine = updPreSta.executeUpdate();
            if (updLine > 0) {
                System.out.println("修改成功！");
                //addToList(cnt);
                select();
            } else {
                System.out.println("修改失败！");
            }
            break;
        }
    }

    //添加
    private static void insert() throws SQLException {
        //添加语句
        String insSQL = """
                insert into tb_brand (brand_name, company_name, ordered, description, status)
                values (?, ?, 1, ?, 1);
                """;

        PreparedStatement insPreSta = cnt.prepareStatement(insSQL);
        System.out.print("输入要添加的品牌名：");
        String grandName = sc.nextLine();
        System.out.print("输入要添加的企业名：");
        String companyName = sc.nextLine();
        System.out.print("输入品牌描述：");
        String description = sc.nextLine();
        insPreSta.setString(1, grandName);
        insPreSta.setString(2, companyName);
        insPreSta.setString(3, description);

        int insLine = insPreSta.executeUpdate();
        if (insLine > 0) {
            System.out.println("添加成功！");
            //addToList(cnt);
            select();
        } else {
            System.out.println("添加失败！");
        }

    }

    //查询
    private static void select() throws SQLException {
        addToList();
        //遍历集合
        for (Brand brand : brandList) {
            System.out.println(brand.toString());
        }
    }

    //读取数据添加到集合
    private static void addToList() throws SQLException {
        //清空集合
        brandList.clear();
        //查询语句
        String selSQL = "select * from tb_brand;";
        Statement sta = cnt.createStatement();
        ResultSet selResults = sta.executeQuery(selSQL);
        //读取数据加入集合
        while (selResults.next()) {
            int id = selResults.getInt("id");
            String brandName = selResults.getString("brand_name");
            String companyName = selResults.getString("company_name");
            int ordered = selResults.getInt("ordered");
            String description = selResults.getString("description");
            int s = selResults.getInt("status");
            boolean status = s == 1;
            //加入集合
            brandList.add(new Brand(id, brandName, companyName, ordered, description, status));
            idList.add(id);
        }
    }
}
